﻿using Dapper;
using Newtonsoft.Json;
using Paging;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace Ordering.API.Application.Queries
{
    public class OrderQueries : IOrderQueries
    {
        private string _connectionString = string.Empty;

        public OrderQueries(string constr)
        {
            _connectionString = !string.IsNullOrWhiteSpace(constr) ? constr : throw new ArgumentNullException(nameof(constr));
        }

        public async Task<OrderModel> GetOrderAsync(int id)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return await connection.QuerySingleAsync<OrderModel>(
                   @"select * from order where Id=@id"
                        , new { id }
                    );

            }
        }
        /// <summary>
        /// 过滤订单
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public async Task<PaginatedItemsViewModel<OrderModel>> GetOrdersAsync(OrderFilter filter)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                string sql = @"SELECT [Id]
                    ,[OrderNO]
                    ,[OrderType]
                    ,[CreateTime]
                    ,[UpdateTime]
                    ,[AppointmentTime]
                    ,[CustomerCode]
                    ,[OrderStatusId]
                    ,[Description]
                    ,[Remark]
                    FROM [OrderingDb].[ordering].[order]";
                var order = await filter.PagingFilterAsync(connection, sql, new string[] { "OrderNO" });
                //查询明细
                if (order.Data.Any())
                {
                    int[] orderIds = order.Data.Select(t => t.Id).ToArray();
                    sql = @"select ProductCode,
                        ProductName,
                        ProductPrice,
                        ExtendField,
                        ExtendField2,
                        Num,Remark,
                        PictureId from [OrderingDb].[ordering].[orderitem] where OrderId in @orderIds";
                   var orderItems = await connection.QueryAsync<OrderItemModel>(sql, new { orderIds = orderIds });
                    order.Data.ToList().ForEach(t =>
                    {
                        t.OrderItems = orderItems.Where(k => k.OrderId == t.Id);
                    });
                }
                return order;
            }

        }
        /// <summary>
        /// 过滤汇总订单
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public async Task<OrderSummaryModel> GetOrderSummaryAsync(OrderFilter filter)
        {
            var filter2 = JsonConvert.DeserializeObject<OrderFilter>(JsonConvert.SerializeObject(filter));
            filter.OrderStatusId = null;
            var orderStatusNum = await GetOrderStatusNum(filter);
            
            var paginated = await GetOrdersAsync(filter2);

            OrderSummaryModel model = new OrderSummaryModel(paginated.PageIndex, paginated.PageSize, paginated.Count,
                orderStatusNum, paginated.Data);
            return model;

        }
        /// <summary>
        /// 查询各状态订单数量
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public async Task<IEnumerable<OrderStatusNum>> GetOrderStatusNum(OrderFilter filter)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                string sql = @"SELECT OrderStatusId,COUNT(*) OrderNum
                    FROM [OrderingDb].[ordering].[order] WHERE ";
                sql += filter.BuildSqlByModel() + "GROUP BY OrderStatusId ";
                var result = await connection.QueryAsync<OrderStatusNum>(sql, filter);
                return result;
            }
        }
    }
}
